1. Field of the Invention
The present invention relates to systems and methods for performing queries on data stored in a database, and in particular to a method and apparatus for using Java as a stored procedure language and as an embedded language on a client.
2. Description of the Related Art
Databases use stored procedures to execute a set of tasks that an application requires inside a database engine. These stored procedures have to be written in some programming language. The prior art does not provide the ability to utilize the Java programming language as the stored procedure programming language. The field of the invention and the prior art may be better understood by describing databases, stored procedures, and the Java programming language.
Databases
The ability to manage massive amounts of information has become a virtual necessity in business today. The information and data are often stored in related files. A set of related files is referred to as a database. A database management system (DBMS) creates and manages one or more databases. Today, DBMSs can manage any form of data including text, images, sound and video. Further, large-scale integrated DBMS"" provide an efficient, consistent, and secure means for storing and retrieving the vast amounts of data.
Certain computer languages have been developed and utilized to interact with and manipulate the data. For example, SQL (Structured Query Language) is a language used to interrogate and process data in a relational database (a database in which relationships are established between files and information stored in the database). Originally developed for mainframes, most database systems designed for client/server environments support SQL. SQL commands can be used to interactively work with a database or can be embedded within a programming language to interface to a database. Thus, methods and functions may embed and utilize SQL commands.
A client-server environment provides the ability to split an application or responsibilities into different locations/computers. Half of an application may run on a client machine and the other half may run on a server machine. For example, data may be stored on a server and the client is provided with an interface to interact with and modify or manipulate the data. To interface with a server-side database from the client, a client typically utilizes embedded programming languages such as COBOL.
Stored Procedures
Users/programmers often need to invoke the same set of commands (or the same set of commands with different parameters) at varying times and locations of a program. In such a situation, the query may be placed into a stored procedure. A stored procedure is a batch of SQL statements stored in a database/on a server, that may be partially or fully processed/compiled before it is stored (or upon its first invocation). Additionally, a stored procedure is a method or procedure written in a programming language that is partially or fully processed/compiled before it is stored (or upon its first invocation).
Stored procedures may be called directly from a client or from a database trigger and are often stored on the server. A database trigger is a user defined mechanism that causes a stored procedure to automatically initiate and execute upon the occurrence of the user specified events in the database (i.e., when the trigger xe2x80x9cfiresxe2x80x9d). Thus, the trigger will not xe2x80x9cfirexe2x80x9d unless the event(s) specified by the user occurs. For example, a user may define a trigger to automatically xe2x80x9cfirxe2x80x9d whenever a user updates, deletes, or inserts data.
Since the stored procedure is stored on a server, the stored procedure is available to all clients and does not need to be replicated in each client. Further, by storing the stored procedure on the server, when the stored procedure is modified, all clients automatically get/have access to the new version. This saves programming effort especially when different client user interfaces and development systems are used. Further, this allows stored procedures to be an easy mechanism for sharing complex queries and functions between multiple applications. Additionally, SQL and stored procedures may call other stored procedures and may be written independently from (and without knowledge of) the underlying DBMS.
A stored procedure may be partially or completely processed/compiled before it is stored on the database. Consequently, the stored procedure does not have to be parsed and compiled each time it is invoked. Further, because a stored procedure is stored in a compiled format, it executes faster than if its constituent commands were executed individually.
Alternatively, a stored procedure may not be compiled prior to storage but may be automatically compiled the first time the procedure is invoked. As part of such a compilation, a query execution plan may be generated. The query execution plan describes the order in which tables are to be accessed and the indexes to be used. Further, the query execution plan is optimized for the stored procedure parameters and data in the database tables at the time the stored procedure is first executed.
A stored procedure may be invoked by its name. The caller can pass parameters to and receive results from the stored procedure. A user can create and name a stored procedure to execute specific database queries and perform other database tasks. For example, a user may create a stored procedure that returns the number of videos of a particular movie remaining in a video store for the video title that is specified at the time the stored procedure is called.
Stored procedures may also maintain the integrity of the database and prevent unauthorized users from modifying certain entries. For example, a user may be given the right to call a stored procedure that updates a table or set of tables but denied the right to update the tables directly.
Stored procedures may be created using a variety of mechanisms. The following format may be utilized to declare a stored procedure:
create proc {procedure name} as
{statement of block of statements}
For example the following stored procedure called myproc will return the number of Casablanca videos left in a video store as well as other movie titles, the rental price of those movie titles, and the location of those videos when Humphrey Bogart is an actor in the movie:
As described above, stored procedures can also be passed parameters.
Parameters may be defined as part of the stored procedure creation statement. The syntax of a xe2x80x9ccreate procxe2x80x9d command with parameters is:
For example, the following stored procedure may be passed the @mytitle and @myactor parameters for use in the select query:
Once a stored procedure has been created, a user can invoke the stored procedure using the following syntax:
exec [database.owner.] {procname} {opt params}
In the above syntax, xe2x80x9cdatabasexe2x80x9d and xe2x80x9cownerxe2x80x9d will default to the current database and the current dbo (database owner). For example, the following command may invoke the stored procedure xe2x80x9cmyprocxe2x80x9d defined above:
exec myproc xe2x80x9cCasablancaxe2x80x9d, xe2x80x9cHumphrey Bogartxe2x80x9d
In this example, the user would see the same results as if the following command were utilized:
Additionally, the xe2x80x9cexecxe2x80x9d portion of an invocation of a stored procedure is not necessary if the stored procedure call is the first line in a batch.
Stored procedures can also have a xe2x80x9creturnxe2x80x9d status. A xe2x80x9creturnxe2x80x9d statement returns from the stored procedure with an optional status parameter. The return status is zero for success, or negative otherwise. Negative values between xe2x88x921 and xe2x88x9299 are reserved. For example, the following stored procedure returns a negative value (xe2x88x92999) if no rows/records are in the result set and a 0 if rows/records are in the result set:
The following commands illustrate the invocation of the above stored procedure with a return status:
In addition to providing parameters when invoking a stored procedure, default values for the parameters may be specified:
The above stored procedure provides a default value of 0 for @myparam1, a default value of null for @myparam2, and a default value of xe2x80x9cmydefaultxe2x80x9d for @myparam3. To invoke the stored procedure, the user may specify the values for one or more parameters, if desired, or may allow one or more of the default values to be utilized:
exec myproc 1 /* params 2 and 3 default */
In the above invocation, only @myparam1 is specified in the execution line. Consequently, the specified default values for @myparam2 and @myparam3 are utilized (i.e., null and xe2x80x9cmydefaultxe2x80x9d).
Additionally, stored procedures can have output parameters as demonstrated below:
The output for the above stored procedure is the number of rows (i.e., the count) in the titles table where the value of x is greater than @myparam1.
Java Programming Language
The Java programming language is a popular programming language originally developed by Sun Microsystems, Inc. The Java programming language features object-oriented programming, portability, and safe execution.
As with many other programming languages, the Java programming language uses a compiler to convert human-readable source code into executable programs. A Java compiler generates architecture independent bytecodes instead of code that can be executed by particular hardware. The bytecodes can be executed by a Java Virtual Machine Java VM), an idealized processor chip usually implemented in software rather than hardware.
Due to its popularity and wide use throughout the world, it is desirable to utilize the Java programming language in a variety of applications. One such application is that of stored procedures. However, the prior art does not provide the ability to utilize the Java programming language as a stored procedure language to interact with a relational database.
To address the requirements described above, the present invention discloses a method, apparatus, and an article of manufacture for using the Java programming language as a stored procedure language and as an embedded language on a client.
Databases used stored procedures to execute a set of tasks that the application requires inside of a database engine. The set of tasks is recorded in a stored procedure and may be executed by the database every time a trigger xe2x80x9cfiresxe2x80x9d. One or more embodiments of the invention provide the ability to utilize the Java programming language as the stored procedure language. In such embodiments the same stored procedure can be used both on the server side and the client side. This allows an application developer to test the stored procedure code on the client side and when the code is debugged, it can be sent to the server, where it runs within the database server.
One or more embodiments of the invention provide for Java abstract data types (ADT) that map to ADT attributes from a database. The Java ADT can then be manipulated in an application written in the Java programming language such as a stored procedure. In such embodiments, each ADT attribute from a database is mapped to a Java class by wrapping the ADT definition (which is commonly written in C ++) in a Java wrapper. The wrapping process enables a user to write any client side Java application.
Once the ADTs are wrapped and a stored procedure is created, the stored procedure must be able to execute. In one or more embodiments of the invention, enhancements permit a stored procedure written in the Java programming language to execute. Such enhancements include a server side cursor that the stored procedure can use to run SQL queries. Additionally, the server has been enhanced to link to a Java virtual machine that can be used to execute the bytecodes of the stored procedure written in the Java programming language.